﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace ParseAndPopulate
{
    //http://www.daniweb.com/software-development/vbnet/code/216920/sql-in-vb.net
    static class SQLcontroller
    {
        //http://msdn.microsoft.com/en-us/library/ff648339.aspx#paght000002_step1
        static string SQLsanitized(string query)
        {
            query = query.Replace("'", "''");
            return query;
        }

        public static void SQLwrite(string SQLStr, string server_address, string db_name, string username, string pwd)
        {
            //Declare inside of class >
            string ConnString = "";
            //Connstring = Server Name, Database Name, Windows Authentication 


            //ConnString = string.Format"Server=108.175.158.161;Database=soen341dB;Uid=soen341user;Pwd=softwareprocess";
            ConnString = string.Format("Server={0};Database={1};Uid={2};Pwd={3}", server_address, db_name, username, pwd);
            //Write to SQL

            SqlConnection SQLConn = new SqlConnection(ConnString); //The SQL Connection
            SqlCommand SQLCmd = new SqlCommand(); //The SQL Command

            SQLConn.ConnectionString = ConnString; //Set the Connection String
            SQLConn.Open(); //Open the connection

            SQLCmd.Connection = SQLConn; //Sets the Connection to use with the SQL Command
            SQLCmd.CommandText = SQLStr;
            //SQLCmd.CommandText = SQLsanitized(SQLStr); //Sets the SQL String
            SQLCmd.ExecuteNonQuery(); //Executes SQL Commands Non-Querys only

            SQLConn.Close(); //Close the connection  
        }

        static public List<string> SQLread(string query, string server_address, string db_name, string username, string pwd)
        {
            string ConnString = string.Format("Server={0};Database={1};Uid={2};Pwd={3}", server_address, db_name, username, pwd);
            SqlConnection sqlConnection1 = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = query;
            //cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();

            reader = cmd.ExecuteReader();
            // Data is accessible through the DataReader object here.
            List<string> gotten = new List<string>();
            while (reader.Read())
            {
                string xml_returned = reader[0].ToString();
                gotten.Add(xml_returned);
            }

            
            sqlConnection1.Close();
            return gotten;
        }
    }     
}
